﻿// Copyright: 2012 
// Author: Minh Vu - YoungJ
// File name: Utilites.cs
// Solution: iTunesApp
// Project: iTunesApp
// Time: 10:10 AM 11/05/2012

using System.Collections.Generic;
using System.Data.OleDb;
using System.Diagnostics;
using System.Linq;
using System.Text.RegularExpressions;
using System.Windows.Forms;

namespace iTunesApp
{
    public static class Utilites
    {

        public static bool isValidUrl(this string url)
        {
            const string pattern = @"^(http|https|ftp)\://[a-zA-Z0-9\-\.]+\.[a-zA-Z]{2,3}(:[a-zA-Z0-9]*)?/?([a-zA-Z0-9\-\._\?\,\'/\\\+&%\$#\=~])*[^\.\,\)\(\s]$";
            var reg = new Regex(pattern, RegexOptions.Compiled | RegexOptions.IgnoreCase);
            return reg.IsMatch(url);
        }

        public static void ShowMessage(string msg)
        {
            MessageBox.Show(msg, "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
        public static void ShowMessageError(string msg)
        {
            MessageBox.Show(msg, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
        public static DialogResult ShowQuestionMessage(string msg)
        {
            return MessageBox.Show(msg, "Question", MessageBoxButtons.YesNo, MessageBoxIcon.Stop);
        }


        public static void ExportToExcel(string filename, IEnumerable<Request> requests)
        {
            using (var connection =
                new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filename + ";Extended Properties=Excel 8.0;"))
            {

                connection.Open();
                var cmd =
                    new OleDbCommand(
                        @"create table [Sheet1] 
                                (STT int, 
                                Artist nvarchar(100), 
                                AlbumSingle nvarchar(100),
                                BackupAccount nvarchar(100), 
                                FBAlbum nvarchar(100), 
                                Url nvarchar(100));", connection);
                cmd.ExecuteNonQuery();
                var stt = 1;
                foreach (var query in requests.Select(item => string.Format("insert into [Sheet1$] values "
                          + "({0},'{1}','{2}','{3}','{4}','{5}')",
                                                        stt++,
                                                        item.Artist,
                                                        item.AlbumSingle,
                                                        item.BackupAccount,
                                                        item.FBAccount,
                                                        item.Url)))
                {
                    cmd = new OleDbCommand(query, connection);
                    cmd.ExecuteNonQuery();

                }
                connection.Close();
                connection.Dispose();
            }
        }

        public static IEnumerable<Request> ImportFromExcel(string filename)
        {
            using (var connection =
                new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filename + ";Extended Properties=Excel 8.0;"))
            {
                connection.Open();
                var cmd = new OleDbCommand("select * from [Sheet1$]", connection);

                var rdr = cmd.ExecuteReader();

                if (rdr == null) return null;
                var list = new List<Request>();
                while (rdr.Read())
                {
                    list.Add(new Request
                                 {
                                     Artist = rdr.MyGetValue(1),
                                     AlbumSingle = rdr.MyGetValue(2),
                                     BackupAccount = rdr.MyGetValue(3),
                                     FBAccount = rdr.MyGetValue(4),
                                     Url = rdr.MyGetValue(5)
                                 });
                }

                rdr.Close();
                rdr.Dispose();
                connection.Close();
                connection.Dispose();
                return list;
            }
        }

        public static string MyGetValue(this OleDbDataReader rdr, int index)
        {
            return rdr.FieldCount > index ? rdr.GetValue(index).ToString() : string.Empty;
        }
    }
}